DROP FUNCTION cumple_prerequisito(numcontrol varchar, clavmateria varchar);
CREATE OR REPLACE FUNCTION fncumple_prerequisito(vnumcontrol varchar, vclavmateria varchar) RETURNS BOOLEAN AS $$
BEGIN
	--checamos si tiene requisito
	
	PERFORM * FROM reticula_prerequisito WHERE clavmateria=vclavmateria;
	IF NOT FOUND THEN
		RETURN TRUE;
	ELSE
		--ahora si, veamos si cumple prerequisito
		PERFORM clavmateriareq FROM reticula_prerequisito WHERE clavmateria=vclavmateria
		AND clavmateriareq IN (SELECT clavmateria FROM reticula_kardex WHERE numcontrol=vnumcontrol);

		IF NOT FOUND THEN
			RETURN FALSE;
		ELSE
			RETURN TRUE;
		END IF;
	END IF;
END;
$$ LANGUAGE plpgsql;

--retorna creditos
CREATE OR REPLACE FUNCTION fncreditos(vnumcontrol varchar) RETURNS int AS $$
BEGIN
	return (select SUM(creditos) from reticula_kardex join reticula_materia
	on reticula_kardex.clavmateria = reticula_materia.clavmateria
	where numcontrol=vnumcontrol);
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION fncalificacion(vnumcontrol varchar, vclavmateria varchar);
CREATE OR REPLACE FUNCTION fncalificacion(vnumcontrol varchar, vclavmateria varchar) RETURNS int AS $$
BEGIN
	return (SELECT calificacion FROM reticula_kardex WHERE numcontrol=vnumcontrol and clavmateria=vclavmateria);
END;
$$ LANGUAGE plpgsql;

select fncalificacion('5032834','BM1');
--consulta

select * from reticula_materia 
where numespecialidad = 14 and semrequisitos<=3
and clavmateria not in (select clavmateria from reticula_kardex where numcontrol='5032834')
and fncumple_prerequisito('5032834', clavmateria);

DROP FUNCTION fnoportunidad(vnumcontrol varchar, vclavmateria varchar);
CREATE OR REPLACE FUNCTION fnoportunidad(vnumcontrol varchar, vclavmateria varchar) RETURNS int AS $$
BEGIN
	RETURN (SELECT oportunidad FROM reticula_kardex WHERE numcontrol=vnumcontrol and clavmateria=vclavmateria);
END;
$$ LANGUAGE plpgsql;

SELECT reticula_materia.clavmateria, numespecialidad, reticula_materia.semestre, creditos, 
	horas_teoricas, horas_practicas, nombre, fnoportunidad('5032834',clavmateria) as oportunidad
	FROM reticula_materia WHERE numespecialidad=14 and semestre = 1
	